package com.lian.dao.impl;
import com.lian.dao.productdao;
import com.lian.dao.staffdao;
import com.lian.dao.workdao;
import com.lian.entity.work;
import com.lian.util.DataBase;

import java.util.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
 * Created by lenovo on 2018/8/6.
 */
public class workdaoimpl implements workdao
{
    //修改时根据id得到值
    public work findworkid(int workid) {
        work wo=new work();
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        String sql = "select*from work where workid=?";
        try {
            conn = DataBase.getConnection();
            stat = conn.prepareStatement(sql);
            stat.setInt(1,workid);
            rs = stat.executeQuery();
            staffdao dao=new staffdaoimpl();
            productdao dao2=new productdaoimpl();
            while (rs.next()) {
                wo.setWorkid(rs.getInt("workid"));
                wo.setStaffid(rs.getInt("staffid"));
                wo.setStaid(dao.findstaff(rs.getInt("staffid")));//用户类型表外键
                wo.setUserName(rs.getString("userName"));
                wo.setUserPhone(rs.getString("userPhone"));
                wo.setUserAddress(rs.getString("userAddress"));
                wo.setWorkType(rs.getInt("workType"));
                wo.setStatus(rs.getInt("status"));
                wo.setWpid(rs.getInt("wpid"));
                wo.setProid(dao2.findpro(rs.getInt("wpid")));//外键
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataBase.closeConnection(conn,stat,rs);
        }
        return wo;
    }

    @Override
    public int addwork(work wo) {
        String sql="insert into work values(null,?,?,?,?,?,?,?)";
        Object[]ob={
                wo.getStaffid(),
                wo.getUserName(),
                wo.getUserPhone(),
                wo.getUserAddress(),
                wo.getWorkType(),
                wo.getStatus(),
                wo.getWpid()
        };
        return DataBase.executeUpdate(sql,ob);
    }

    @Override
    public int updwork(work wo) {

        String sql="update work set staffid=?,UserName=?,userPhone=?,userAddress=?,workType=?,status=?,wpid=? where workid=?";
        Object[]ob={
                wo.getStaffid(),
                wo.getUserName(),
                wo.getUserPhone(),
                wo.getUserAddress(),
                wo.getWorkType(),
                wo.getStatus(),
                wo.getWpid(),
                wo.getWorkid()
        };
        return DataBase.executeUpdate(sql,ob);
    }

    @Override
    public int delwork(int workid) {

        String sql="delete from  work where workid=?";
        Object[]ob={
                workid
        };
        return DataBase.executeUpdate(sql,ob);
    }

    @Override
    public List<work> findwork(int page, int size) {
        List<work>list=new ArrayList<work>();
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        String sql = "select*from work limit ?,?";
        try {
            conn = DataBase.getConnection();
            stat = conn.prepareStatement(sql);
            stat.setInt(1, page);
            stat.setInt(2, size);
            rs = stat.executeQuery();
            staffdao dao=new staffdaoimpl();
            productdao dao2=new productdaoimpl();
            while (rs.next()) {
                work wo=new work();
                wo.setWorkid(rs.getInt("workid"));
                wo.setStaffid(rs.getInt("staffid"));
                wo.setStaid(dao.findstaff(rs.getInt("staffid")));//用户类型表外键
                wo.setUserName(rs.getString("userName"));
                wo.setUserPhone(rs.getString("userPhone"));
                wo.setUserAddress(rs.getString("userAddress"));
                wo.setWorkType(rs.getInt("workType"));
                wo.setStatus(rs.getInt("status"));
                wo.setWpid(rs.getInt("wpid"));
                wo.setProid(dao2.findpro(rs.getInt("wpid")));//外键
                list.add(wo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DataBase.closeConnection(conn,stat,rs);
        }
        return list;
    }

    @Override
    //获取phone表的总行数
    public int findCount() {
        int count=0;
        String sql ="select count(1) from work";
        Connection conn = null;
        PreparedStatement stat=null;
        ResultSet rs = null;
        try {
            conn  = DataBase.getConnection();
            stat = conn.prepareStatement(sql);
            rs = stat.executeQuery();
            if(rs.next()){
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            // TODO: handle exception
        }finally{
            DataBase.closeConnection(conn, stat, rs);
        }
        return count;
    }
}
